﻿using System;
using System.Data.Odbc;
using System.Data;
using personalplaner.core.errorhandling;
using personalplaner.business.common;

namespace personalplaner.core.sql
{
	public class SimpleQuerry
	{
		//static OdbcConnection m_odbcConnection = null;
		//static int m_odbcTimeout = -1;

		///// <summary>
		///// Executes a single SQLString to the given conection in DatabaseConection.ODBCConection or this.ODBCConection
		///// </summary>
		///// <param name="sqlString">String to be executed in conection</param>
		///// <returns>False if an error occured</returns>
		//public static bool Execute(string sqlString, DatabaseSettings ODBCConnection)
		//{
		//    return Execute(sqlString, false);
		//}

		/// <summary>
		/// Executes a single SQLString to the given conection in DatabaseConection.ODBCConection or this.ODBCConection
		/// </summary>
		/// <param name="settings"></param>
		/// <param name="sqlString">String to be executed in conection</param>
		/// <param name="log">logs the executed sql string</param>
		/// <returns>False if an error occured</returns>
		public static bool Execute(DatabaseSettings settings, string sqlString, bool log = false)
		{
			if (log)
				ActionLogger.LoggMessage(MessageType.SQL, sqlString);

			OdbcCommand cmd = null;
			try
			{
				cmd = new OdbcCommand(sqlString, settings.Connection);
			}
			catch (OdbcException e)
			{
				Logger.ToXML(e.Message.ToString());
				return false;
			}

			try
			{
				settings.Connection.Open();
				cmd.ExecuteNonQuery();
			}
			catch (Exception objError)
			{
				Logger.ToXML(objError.Message.ToString() + "\nQuery:\n" + sqlString);
				if (!log)
					ActionLogger.LoggMessage(MessageType.SQL_ERROR, sqlString);
				ActionLogger.LoggMessage(MessageType.SQL_ERROR, objError.Message.ToString());
				return false;
			}
			finally
			{
				cmd.Dispose();
				settings.Connection.Close();
			}

			return true;
		}

        /// <summary>
        /// Executes a single SQLString to the given conection in DatabaseConection.ODBCConection or this.ODBCConection
        /// </summary>
		/// <param name="settings"></param>
        /// <param name="sqlString">String to be executed in conection</param>
        /// <param name="log">logs the executed sql string</param>
        /// <returns>A DataView with the information from the Database.
        /// Empty if an error occurs</returns>
		public static DataTable ExecuteForDataTable(DatabaseSettings settings, string sqlString, bool log = false)
        {
            DataTable dt = null;
            DataSet ds = new DataSet();
            OdbcCommand com = null;

            if (log)
				ActionLogger.LoggMessage(MessageType.SQL, sqlString);
            try
            {
				if (settings.Connection.State == ConnectionState.Closed)
					settings.Connection.Open();

				com = new OdbcCommand(sqlString, settings.Connection);
                if (settings.Timeout >= 0)
                    com.CommandTimeout = settings.Timeout;
                //cmd.SelectCommand.Connection.ConnectionTimeout = OdbcTimeout;

            }
            catch (OdbcException e)
            {
                Logger.ToXML(e.Message.ToString() + "\nQuery:\n" + sqlString);
                if (!log)
					ActionLogger.LoggMessage(MessageType.SQL_ERROR, sqlString);
				ActionLogger.LoggMessage(MessageType.SQL_ERROR, e.Message.ToString());
                return null;
            }

            try
            {
                //cmd.Fill(ds, "Daten");
                //dv = new DataView(ds.Tables["Daten"]);

                DataReaderAdapter dra = new DataReaderAdapter();
                dt = new DataTable();

                OdbcDataReader reader = com.ExecuteReader();
                dra.FillFromReader(dt, reader);

                reader.Close();
            }
            catch (OdbcException exp)
            {
                ds.Dispose();
                Logger.ToXML(exp.Message.ToString());
                if (log)
					ActionLogger.LoggMessage(MessageType.SQL, sqlString);
            }
            catch (OutOfMemoryException exp)
            {
                ds.Dispose();
                Logger.ToXML(exp.Message.ToString());
            }
            finally
            {
				settings.Connection.Close();

                //cmd.Dispose();
                com.Dispose();
                ds.Dispose();
            }

            return dt;
        }
		

		//public static void OpenConnection(DatabaseSettings settings)
		//{
		//    if (settings != null)
		//        settings.Connection.Open();
		//}

		//public static void CloseConnection(DatabaseSettings settings)
		//{
		//    if (settings != null)
		//        settings.Connection.Close();
		//}

		#region Properties
		///// <summary>
		///// OdbcConnection object
		///// </summary>
		//public static OdbcConnection ODBCConnection
		//{
		//    get
		//    {
		//        if (m_odbcConnection == null)
		//        {
		//            if (m_odbcTimeout >= 0)
		//                if (DatabaseConection.ODBCConnection != null)
		//                    DatabaseConection.ODBCConnection.ConnectionTimeout = m_odbcTimeout;
		//            return DatabaseConection.ODBCConnection;
		//        }

		//        return m_odbcConnection;
		//    }
		//    set
		//    {
		//        if (m_odbcConnection != null)
		//        {
		//            m_odbcConnection.Dispose();
		//            m_odbcConnection = null;
		//        }

		//        m_odbcConnection = value;
		//        if (m_odbcTimeout >= 0)
		//            m_odbcConnection.ConnectionTimeout = m_odbcTimeout;
		//    }
		//}

		//public static int OdbcTimeout
		//{
		//    get
		//    {
		//        return m_odbcTimeout;
		//    }
		//    set
		//    {
		//        m_odbcTimeout = value;

		//        if (ODBCConnection != null)
		//            ODBCConnection.ConnectionTimeout = value;
		//    }
		//}

		#endregion
	}
}
